package zy.dao.sell.dayend.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.sell.dayend.DayEndDAO;
import zy.entity.sell.card.T_Sell_CardList;
import zy.entity.sell.dayend.T_Sell_DayEnd;
import zy.form.NumberForm;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.NumberUtil;
import zy.util.StringUtil;

@Repository
public class DayEndDAOImpl extends BaseDaoImpl implements DayEndDAO {

	@Override
	public void save(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		T_Sell_DayEnd existDayEnd = null;
		sql.append(" SELECT de_id ");
		sql.append(" FROM t_sell_dayend");
		sql.append(" WHERE de_em_code=:emp_code");
		sql.append(" AND de_shop_code = :shop_code");
		sql.append(" AND de_state = 0");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			existDayEnd = namedParameterJdbcTemplate.queryForObject(sql.toString(),param,new BeanPropertyRowMapper<>(T_Sell_DayEnd.class));
		}catch(Exception e){
		}
		if(existDayEnd != null){//已存在未日结的记录，则不再保存，直接返回
			return;
		}
		sql.setLength(0);
		sql.append(" INSERT INTO t_sell_dayend(");
		sql.append(" de_em_code,de_shop_code,de_st_code,de_state,companyid,de_begindate,de_man");
		sql.append(" ) VALUES (");
		sql.append(" :emp_code,:shop_code,:st_code,0,:companyid,:sysdate,:de_man");
		sql.append(" )");
		namedParameterJdbcTemplate.update(sql.toString(), param);
	}
	/**
	 * 1.先查询收银员开始时间
	 * 2.统计零售表的数据
	 * 3.统计储值卡、代金券发放
	 * 4.统计会员信息
	 * */
	@Override
	public T_Sell_DayEnd query(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT de_id,de_begindate");
		sql.append(" FROM t_sell_dayend t");
		sql.append(" WHERE t.de_em_code=:emp_code");
		sql.append(" AND de_state=0");
		sql.append(" AND de_st_code=:st_code");
		sql.append(" AND t.de_shop_code=:shop_code");
		sql.append(" AND t.companyid=:companyid");
		T_Sell_DayEnd dayEnd = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Sell_DayEnd.class));
		if(null != dayEnd){
			param.put("begindate", dayEnd.getDe_begindate());
			dayEnd.setDe_enddate(DateUtil.getCurrentTime());
			sql.setLength(0);
			sql.append(" SELECT COUNT(1) de_bills,");
			sql.append(" SUM(sh_amount) de_amount,");
			sql.append(" SUM(sh_money) de_money,");
			sql.append(" SUM(sh_cash) de_cash,");
			sql.append(" SUM(sh_ali_money) de_ali_money,");
			sql.append(" SUM(sh_wx_money) de_wx_money,");
			sql.append(" SUM(sh_cd_money) de_cd_money,");
			sql.append(" SUM(sh_vc_money) de_vc_money,");
			sql.append(" SUM(sh_sd_money) de_sd_money,");
			sql.append(" SUM(sh_ec_money) de_ec_money,"); 
			sql.append(" SUM(sh_point_money) de_point_money,");
			sql.append(" SUM(sh_sell_money) de_sell_money,");
			sql.append(" SUM(sh_mall_money) de_mall_money,");
			sql.append(" SUM(sh_lost_money) de_lost_money,");
			sql.append(" SUM(sh_bank_money) de_bank_money");
			sql.append(" FROM t_sell_shop t");
			sql.append(" WHERE 1=1");
			sql.append(" AND t.sh_sysdate BETWEEN :begindate AND SYSDATE()");
			sql.append(" AND t.sh_em_code=:emp_code");
			sql.append(" AND t.sh_shop_code=:shop_code");
			sql.append(" AND t.companyid=:companyid");
			T_Sell_DayEnd day = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, 
					new BeanPropertyRowMapper<>(T_Sell_DayEnd.class));
			if(null != day){
				dayEnd.setDe_bills(day.getDe_bills());
				dayEnd.setDe_amount(day.getDe_amount());
				dayEnd.setDe_money(day.getDe_money());
				dayEnd.setDe_cash(day.getDe_cash());
				dayEnd.setDe_ali_money(day.getDe_ali_money());
				dayEnd.setDe_wx_money(day.getDe_wx_money());
				dayEnd.setDe_cd_money(day.getDe_cd_money());
				dayEnd.setDe_vc_money(day.getDe_vc_money());
				dayEnd.setDe_point_money(day.getDe_point_money());
				dayEnd.setDe_sell_money(day.getDe_sell_money());
				dayEnd.setDe_mall_money(day.getDe_mall_money());
				dayEnd.setDe_sd_money(day.getDe_sd_money());
				dayEnd.setDe_ec_money(day.getDe_ec_money());
				dayEnd.setDe_bank_money(day.getDe_bank_money());
				dayEnd.setDe_lost_money(day.getDe_lost_money());
			}
			sql.setLength(0);
			sql.append(" SELECT COUNT(1) count,sh_state code");
			sql.append(" FROM t_sell_shop t");
			sql.append(" WHERE 1=1");
			sql.append(" AND t.sh_sysdate BETWEEN :begindate AND SYSDATE()");
			sql.append(" AND t.sh_em_code=:emp_code");
			sql.append(" AND t.sh_shop_code=:shop_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" GROUP BY sh_state");
			List<NumberForm> formList = namedParameterJdbcTemplate.query(sql.toString(), param, 
					new BeanPropertyRowMapper<>(NumberForm.class));
			if(null != formList && formList.size() > 0){
				for(NumberForm form:formList){
					if(form.getCode().equals("0")){
						dayEnd.setDe_sell_bills(form.getCount());
					}
					if(form.getCode().equals("1")){
						dayEnd.setDe_back_bills(form.getCount());
					}
					if(form.getCode().equals("2")){
						dayEnd.setDe_change_bills(form.getCount());
					}
				}
			}
			sql.setLength(0);
			sql.append(" ");
			sql.append(" SELECT cdl_type,COUNT(1) number,SUM(cdl_realcash) cdl_realcash,SUM(cdl_bankmoney) cdl_bankmoney");
			sql.append(" FROM t_sell_cardlist");
			sql.append(" WHERE 1=1");
			sql.append(" AND cdl_type IN (0,1)");
			sql.append(" AND cdl_manager=:emp_name");
			sql.append(" AND cdl_date BETWEEN :begindate AND SYSDATE()");
			sql.append(" AND cdl_shop_code=:shop_code");
			sql.append(" AND companyid=:companyid");
			sql.append(" GROUP BY cdl_type");
			//储值卡数据统计
			List<T_Sell_CardList> cardList = namedParameterJdbcTemplate.query(sql.toString(), param, 
					new BeanPropertyRowMapper<>(T_Sell_CardList.class));
			if(null != cardList && cardList.size() > 0){
				for(T_Sell_CardList card:cardList){
					if(card.getCdl_type() == 0){
						dayEnd.setDe_cd_sends(card.getNumber());
						dayEnd.setDe_cd_send_bank(card.getCdl_bankmoney());
						dayEnd.setDe_cd_send_cash(card.getCdl_realcash());
					}
					if(card.getCdl_type() == 1){
						dayEnd.setDe_cd_fill_bank(card.getCdl_bankmoney());
						dayEnd.setDe_cd_fill_cash(card.getCdl_realcash());
					}
				}
			}
			//查询代金券的统计
			sql.setLength(0);
			sql.append(" SELECT COUNT(1) count,SUM(vcl_cash) number");
			sql.append(" FROM t_sell_voucherlist");
			sql.append(" WHERE 1=1");
			sql.append(" AND vcl_type=0");
			sql.append(" AND vcl_manager=:emp_name");
			sql.append(" AND vcl_date BETWEEN :begindate AND SYSDATE()");
			sql.append(" AND vcl_shop_code=:shop_code");
			sql.append(" AND companyid=:companyid");
			try {
				NumberForm form = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, 
						new BeanPropertyRowMapper<>(NumberForm.class));
				if(null != form){
					dayEnd.setDe_vc_sends(form.getCount());
					dayEnd.setDe_vc_send_money(form.getNumber());
				}
			} catch (Exception e) {
			}
			sql.setLength(0);
			sql.append(" SELECT COUNT(1)");
			sql.append(" FROM t_vip_member");
			sql.append(" WHERE 1=1");
			sql.append(" AND vm_sysdate BETWEEN :begindate AND SYSDATE()");
			sql.append(" AND vm_manager_code=:emp_code");
			sql.append(" AND vm_shop_code=:shop_code");
			sql.append(" AND companyid=:companyid");
			
			Integer vips = namedParameterJdbcTemplate.queryForObject(sql.toString(), param ,Integer.class);
			dayEnd.setDe_vips(vips);
			
			//调出调入的统计
			sql.setLength(0);
			sql.append(" SELECT SUM(ac_amount)");
			sql.append(" FROM t_sell_allocate t");
			sql.append(" WHERE ac_out_shop=:shop_code");
			sql.append(" AND ac_sysdate BETWEEN :begindate AND sysdate()");
			sql.append(" AND t.companyid=:companyid");
			try {
				Integer out_amount = namedParameterJdbcTemplate.queryForObject(sql.toString(), param ,Integer.class);
				dayEnd.setDe_out_amount(out_amount);
			} catch (Exception e) {
			}
			//调入的统计
			sql.setLength(0);
			sql.append(" SELECT SUM(ac_amount)");
			sql.append(" FROM t_sell_allocate t");
			sql.append(" WHERE ac_in_shop=:shop_code");
			sql.append(" AND ac_sysdate BETWEEN :begindate AND sysdate()");
			sql.append(" AND t.companyid=:companyid");
			try {
				Integer out_amount = namedParameterJdbcTemplate.queryForObject(sql.toString(), param ,Integer.class);
				dayEnd.setDe_in_amount(out_amount);
			} catch (Exception e) {
			}
			/*sql.setLength(0);
			sql.append(" SELECT de_stock_amount");
			sql.append(" FROM t_sell_dayend t");
			sql.append(" WHERE 1=1");
			sql.append(" AND de_state=1");
			sql.append(" AND t.de_shop_code=:shop_code");
			sql.append(" AND t.companyid=:companyid");
			sql.append(" LIMIT 1");
			try {
				Integer last_stock = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
				if(null != last_stock && last_stock > 0){
					dayEnd.setDe_last_stock(last_stock);
				}
			} catch (Exception e) {
			}
			sql.setLength(0);
			sql.append(" SELECT SUM(sd_amount)");
			sql.append(" FROM t_stock_data t");
			sql.append(" WHERE sd_dp_code=:dp_code");
			sql.append(" AND t.companyid=:companyid");
			try {
				Integer sd_amount = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
				if(null != sd_amount && sd_amount > 0){
					dayEnd.setDe_stock_amount(sd_amount);
				}
			} catch (Exception e) {
			}*/
			//总现金=收银现金+储值卡充值现金+储值卡发卡现金+发代金券+订金（包含退的）
			double totalCash = NumberUtil.toDouble(dayEnd.getDe_cash());
			totalCash += NumberUtil.toDouble(dayEnd.getDe_cd_fill_cash());
			totalCash += NumberUtil.toDouble(dayEnd.getDe_cd_send_cash());
			totalCash += NumberUtil.toDouble(dayEnd.getDe_vc_send_money());
			totalCash += NumberUtil.toDouble(dayEnd.getDe_sd_send_money());
			totalCash -= NumberUtil.toDouble(dayEnd.getDe_sd_back_money());
			dayEnd.setTotal_cash(totalCash);
			//总刷卡=收银刷卡+储值卡充值刷卡+储值卡发卡刷卡
			double totalBank = NumberUtil.toDouble(dayEnd.getDe_bank_money());
			totalBank += NumberUtil.toDouble(dayEnd.getDe_cd_fill_bank());
			totalBank += NumberUtil.toDouble(dayEnd.getDe_cd_send_bank());
			dayEnd.setTotal_bank(totalBank);
			return dayEnd;
		}else{
			return null;
		}
	}
	@Override
	public void update(T_Sell_DayEnd dayend) {
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE t_sell_dayend SET ");
		sql.append(" de_state=:de_state,");
		sql.append(" de_bills=:de_bills,");
		sql.append(" de_sell_bills=:de_sell_bills,");
		sql.append(" de_back_bills=:de_back_bills,");
		sql.append(" de_change_bills=:de_change_bills,");
		sql.append(" de_sell_money=:de_sell_money,");
		sql.append(" de_amount=:de_amount,");
		sql.append(" de_money=:de_money,");
		sql.append(" de_gifts=:de_gifts,");
		sql.append(" de_vips=:de_vips,");
		sql.append(" de_cash=:de_cash,");
		sql.append(" de_cd_money=:de_cd_money,");
		sql.append(" de_vc_money=:de_vc_money,");
		sql.append(" de_sd_money=:de_sd_money,");
		sql.append(" de_point_money=:de_point_money,");
		sql.append(" de_ec_money=:de_ec_money,");
		sql.append(" de_bank_money=:de_bank_money,");
		sql.append(" de_mall_money=:de_mall_money,");
		sql.append(" de_ali_money=:de_ali_money,");
		sql.append(" de_wx_money=:de_wx_money,");
		sql.append(" de_lost_money=:de_lost_money,");
		sql.append(" de_cd_sends=:de_cd_sends,");
		sql.append(" de_cd_send_cash=:de_cd_send_cash,");
		sql.append(" de_cd_send_bank=:de_cd_send_bank,");
		sql.append(" de_cd_fill_cash=:de_cd_fill_cash,");
		sql.append(" de_cd_fill_bank=:de_cd_fill_bank,");
		sql.append(" de_vc_sends=:de_vc_sends,");
		sql.append(" de_vc_send_money=:de_vc_send_money,");
		sql.append(" de_sd_send_money=:de_sd_send_money,");
		sql.append(" de_sd_back_money=:de_sd_back_money,");
		sql.append(" de_vip_point=:de_vip_point,");
		sql.append(" de_enddate=:de_enddate,");
		sql.append(" de_in_amount=:de_in_amount,");
		sql.append(" de_out_amount=:de_out_amount,");
//		de_stock_amount
		sql.append(" de_in_cash=:de_in_cash,");
		sql.append(" de_in_bank=:de_in_bank");
		sql.append(" WHERE de_id=:de_id");
		namedParameterJdbcTemplate.update(sql.toString(),new BeanPropertySqlParameterSource(dayend));
	}
	@Override
	public Integer count(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);//登录人的类型ID
		String jmd = StringUtil.trimString(paramMap.get("jmd"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1)");
		sql.append(" FROM t_sell_dayend t");
		sql.append(" JOIN t_base_shop sp ON sp_code = de_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			if("1".equals(jmd)){
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_upcode = :shop_code AND sp_shop_type = "+CommonUtil.FOUR);
			}else {
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
			}
		}else{//加盟店
			sql.append(" WHERE 1 = 1");
			sql.append(" AND de_shop_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(paramMap.get("de_shop_code"))){
			paramMap.put("shop_codes", StringUtil.parseList(StringUtil.trimString(paramMap.get("de_shop_code"))));
			sql.append(" AND de_shop_code IN (:shop_codes)");
		}
		if(StringUtil.isNotEmpty(paramMap.get("de_state"))){
			sql.append(" AND de_state=:de_state");
		}
		if(StringUtil.isNotEmpty(paramMap.get("de_em_code"))){
			sql.append(" AND de_em_code=:de_em_code");
		}
		if(StringUtil.isNotEmpty(paramMap.get("begindate"))){
			sql.append(" AND de_begindate BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), paramMap, Integer.class);
	}
	@Override
	public List<T_Sell_DayEnd> list(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);//登录人的类型ID
		String jmd = StringUtil.trimString(paramMap.get("jmd"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append(" (SELECT em_name FROM t_base_emp e WHERE e.em_code=t.de_em_code AND e.companyid=t.companyid LIMIT 1) AS em_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank e WHERE e.ba_code=t.de_in_cash AND e.companyid=t.companyid LIMIT 1) AS cash_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank e WHERE e.ba_code=t.de_in_bank AND e.companyid=t.companyid LIMIT 1) AS bank_name,");
		sql.append(" de_em_code,de_shop_code,de_st_code,de_state,sp_name shop_name,");
		sql.append(" de_bills,de_sell_bills,de_back_bills,de_change_bills,");
		sql.append(" de_sell_money,de_amount,de_money,de_last_money,");
		sql.append(" de_petty_cash,de_gifts,de_vips,de_cash,");
		sql.append(" de_cd_money,de_vc_money,de_sd_money,de_point_money,");
		sql.append(" de_ec_money,de_bank_money,de_mall_money,de_ali_money,");
		sql.append(" de_wx_money,de_lost_money,de_cd_sends,de_cd_send_cash,");
		sql.append(" de_cd_send_bank,de_cd_fill_cash,de_cd_fill_bank,de_vc_sends,");
		sql.append(" de_vc_send_money,de_sd_send_money,de_sd_back_money,de_vip_point,");
		sql.append(" de_begindate,de_enddate,de_man,de_in_amount,de_out_amount,");
		sql.append(" de_in_cash,de_in_bank");
		sql.append(" FROM t_sell_dayend t");
		sql.append(" JOIN t_base_shop sp ON sp_code = de_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			if("1".equals(jmd)){
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_upcode = :shop_code AND sp_shop_type = "+CommonUtil.FOUR);
			}else {
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
			}
		}else{//加盟店
			sql.append(" WHERE 1 = 1");
			sql.append(" AND de_shop_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(paramMap.get("de_shop_code"))){
			paramMap.put("shop_codes", StringUtil.parseList(StringUtil.trimString(paramMap.get("de_shop_code"))));
			sql.append(" AND de_shop_code IN (:shop_codes)");
		}
		if(StringUtil.isNotEmpty(paramMap.get("de_state"))){
			sql.append(" AND de_state=:de_state");
		}
		if(StringUtil.isNotEmpty(paramMap.get("de_em_code"))){
			sql.append(" AND de_em_code=:de_em_code");
		}
		if(StringUtil.isNotEmpty(paramMap.get("begindate"))){
			sql.append(" AND de_begindate BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY de_id DESC");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_DayEnd.class));
	}
	@Override
	public List<T_Sell_DayEnd> listShop(Map<String, Object> paramMap) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT de_id,de_enddate,de_em_code,de_st_code,");
		sql.append(" (select em_name from t_base_emp e where e.em_code=t.de_em_code and e.companyid=t.companyid limit 1) as em_name,");
		sql.append(" (select st_name from t_sell_shift s where s.st_code=t.de_st_code and s.companyid=t.companyid limit 1) as st_name,");
		sql.append(" de_sell_money,de_money");
		sql.append(" FROM t_sell_dayend t");
		sql.append(" where t.companyid=:companyid");
		sql.append(" AND de_state=1");
		sql.append(" AND de_shop_code=:shop_code");
		sql.append(" ORDER BY de_id DESC");
		sql.append(" LIMIT 8");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap,
				new BeanPropertyRowMapper<>(T_Sell_DayEnd.class));
	}
	@Override
	public T_Sell_DayEnd endByID(Integer de_id){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT de_id,de_em_code,de_shop_code,de_st_code,");
		sql.append(" (select sp_name from t_base_shop e where e.sp_code=t.de_shop_code and e.companyid=t.companyid limit 1) as shop_name,");
		sql.append(" (select em_name from t_base_emp e where e.em_code=t.de_em_code and e.companyid=t.companyid limit 1) as em_name,");
		sql.append(" (select st_name from t_sell_shift s where s.st_code=t.de_st_code and s.companyid=t.companyid limit 1) as st_name,");
		sql.append(" de_bills,de_sell_bills,de_back_bills,de_change_bills,");
		sql.append(" de_sell_money,de_amount,de_money,de_last_money,");
		sql.append(" de_petty_cash,de_gifts,de_vips,de_cash,");
		sql.append(" de_cd_money,de_vc_money,de_sd_money,de_point_money,");
		sql.append(" de_ec_money,de_bank_money,de_mall_money,de_ali_money,");
		sql.append(" de_wx_money,de_lost_money,de_cd_sends,de_cd_send_cash,");
		sql.append(" de_cd_send_bank,de_cd_fill_cash,de_cd_fill_bank,");
		sql.append(" de_vc_sends,de_vc_send_money,de_sd_send_money,");
		sql.append(" de_sd_back_money,de_vip_point,de_begindate,");
		sql.append(" de_enddate,de_man,de_in_amount,de_out_amount,");
		sql.append(" de_stock_amount,de_in_cash,de_in_bank");
		sql.append(" FROM t_sell_dayend t");
		sql.append(" WHERE de_id=:de_id");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("de_id", de_id), 
				new BeanPropertyRowMapper<>(T_Sell_DayEnd.class));
	}
}
